﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";

		studentInsert = 0;
		studentSkip = 0;
		studentError = 0;
		
		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);
		
		studentAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		chineseFactory = getProperty("serviceFactory").getBean("chineseFactory");
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		
		sequenceMatrixGroupUser = getProperty("serviceFactory").getBean("sequenceMatrixGroupUser");
		sequenceStudentChange = getProperty("serviceFactory").getBean("sequenceStudentChange");
		
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		chsFactory = getProperty("serviceFactory").getBean("chineseFactory");
		
		termId = event.getArg("TermID");
		tchId = sessionAdvice.getAutherUserID();
		changeType = event.getArg("ChangeTypeCode");
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入专升本数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="专升本数据">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入学籍信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />

				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "性别")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "身份证")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "民族")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "专科学校")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入班级")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入专业")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入学院")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,性别,身份证,民族,专科学校,转入班级,转入专业,转入学院</p>
				</cfif>

				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
				
					<cftransaction>
					
						<!--- 设置学籍数据相关默认值 --->

                        <cfset sql = "SELECT sch_name,sch_citycode FROM t_school " /> 
                            
						<cfset queryObj = new Query( datasource=application.dnsMaster ) />
                        
                        <cfset rs_school = queryObj.execute( sql=sql ).getResult() />  
                        
						<cfset targetName = rs_school.sch_name />
						
						<cfloop query="rs">
						
							<!---准备数据--->
							<cfset studentId = rs["学号"][rs.currentRow] />
							<cfset studentName = rs["姓名"][rs.currentRow] />
							<cfset sex = rs["性别"][rs.currentRow] eq "男" ? 1 : 2 />
							<cfset IDCard = rs["身份证"][rs.currentRow] />
							<cfset nationName = rs["民族"][rs.currentRow] />
							<cfset source = rs["专科学校"][rs.currentRow] />
							<cfset className = rs["转入班级"][rs.currentRow] />
							<cfset sbjName = rs["转入专业"][rs.currentRow] />
							<cfset insName = rs["转入学院"][rs.currentRow] />
							
							<cfset usedName = "" />
							<cfif listFindNoCase(rs.columnList, "曾用名") and len(rs["曾用名"][rs.currentRow])>
								<cfset usedName = rs["曾用名"][rs.currentRow] />
							</cfif>
							
							<cfset birthday = mid(IDCard,7,8) />
							
							<cfif len(IDCard) eq 15>
								<cfset birthday = "19" & mid(IDCard,7,6) />
							</cfif>
							
							<cfif listFindNoCase(rs.columnList, "出生日期") and len(rs["出生日期"][rs.currentRow]) and isNumeric(rs["出生日期"][rs.currentRow]) and len(rs["出生日期"][rs.currentRow]) eq 8>
								<cfset birthday = rs["出生日期"][rs.currentRow] />
							</cfif>
							
							<cfset homeAddress = "" />
							<cfif listFindNoCase(rs.columnList, "家庭地址") and len(rs["家庭地址"][rs.currentRow])>
								<cfset homeAddress = rs["家庭地址"][rs.currentRow] />
							</cfif>
							
							<cfset zipCode = "" />
							<cfif listFindNoCase(rs.columnList, "邮编") and len(rs["邮编"][rs.currentRow])>
								<cfset zipCode = rs["邮编"][rs.currentRow]/>
							</cfif>
							
							<cfset approveDocument = "" />
							<cfif listFindNoCase(rs.columnList, "审批文号") and len(rs["审批文号"][rs.currentRow])>
								<cfset approveDocument = rs["审批文号"][rs.currentRow] />
							</cfif>
							
							<!---查询学生是否存在--->
							<cfset sql = "	SELECT
												stu.stu_id
											FROM
												t_student stu
											WHERE
												stu.stu_id = :studentId " />
							
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name = "studentId", value = studentId, cfsqltype = "cf_sql_varchar")/>
							<cfset rs_student = queryObj.execute(sql=sql).getResult()/>
							
							<!---查询班级、专业、学院信息--->
							<cfset sql = "	SELECT
												clz.cls_id, clz.grade, sbj.level_code, sbj.subject_code, i.institute_id,
												sbj.b_id,sbj.sbj_short,sbj.sbj_name,i.institute_name,i.institute_short
											FROM
												t_class clz INNER JOIN t_subject sbj ON sbj.sbj_id = clz.sbj_id
												INNER JOIN t_institute i ON i.institute_id = sbj.institute_id
											WHERE
												clz.cls_name = :className " />
							
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name = "className", value = className, cfsqltype = "cf_sql_varchar")/>
							<cfset rs_class = queryObj.execute(sql=sql).getResult()/>
							
							<!---查询民族信息--->
							<cfset sql = "	SELECT
												n.nation_code
											FROM
												t_nation n
											WHERE
												n.nation_name = :nationName " />
							
							<cfset queryObj = new Query(datasource = application.dnsMaster)/>
							<cfset queryObj.addParam(name = "nationName", value = nationName, cfsqltype = "cf_sql_varchar")/>
							<cfset rs_nation = queryObj.execute(sql=sql).getResult()/>

							<cfif rs_student.recordCount>
								<p class="success">学生(<cfoutput>#studentName#[#studentId#]</cfoutput>)已经存在!</p>
								<cfset studentSkip++ />
							<cfelseif rs_class.recordCount eq 0>
								<p class="error">班级(#className#)不存在!</p>
								<cfset studentError++ />
							<cfelseif rs_class.recordCount and (rs_class.sbj_name neq sbjName AND rs_class.sbj_short neq sbjName)>
								<p class="error"><cfoutput>班级(#className#)与专业(#sbjName#)不匹配!</cfoutput></p>
								<cfset studentError++ />
							<cfelseif rs_class.recordCount and (rs_class.institute_name neq insName AND rs_class.institute_short neq insName)>
								<p class="error"><cfoutput>班级(#className#)与学院(#insName#)不匹配!</cfoutput></p>
								<cfset studentError++ />
							<cfelseif rs_nation.recordCount eq 0>
								<p class="error">民族(#nationName#)不存在!</p>
								<cfset studentError++ />
							<cfelse>
								<!---开始正常业务--->
								<cfscript>
									
									pinyin = chineseFactory.getPinyinIndex(studentName); 
									englishName = "";
									birthday = replace(birthday, "-", "", "ALL");
									subjectDirection = 0;
									formClass = rs_class.cls_id;
									IDCardType = "01";
									birthPlace = "510703";
									politicalStatus = "13";
									nation = rs_nation.nation_code;
									
									sql = "	INSERT INTO t_student (
												 stu_id,
												 stu_name,
												 stu_pyidx,
												 stu_name_english,
												 stu_name_used,
												 stu_sex,
												 stu_birthday,
												 stu_idcard,
												 sbj_direction,
												 cls_id,
												 idtype,
												 area_code,
												 pa_code,
												 nation_code   
											) VALUES (
												:studentId , 
												:studentName ,
												:Pinyin ,
												:englishName ,
												:usedName ,
												:sex ,
												:birthday ,
												:IDCard ,
												:subjectDirection ,  
												:formClass ,
												:IDCardType ,
												:birthPlace ,
												:politicalStatus ,
												:nation  
											)";

									queryObj = new Query( datasource=application.dnsMaster );
									
									queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="studentName", value=studentName, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="pinyin", value=pinyin, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="englishName", value=englishName, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="usedName", value=usedName, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="sex", value=sex, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="birthday", value=birthday, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="IDCard", value=IDCard, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="subjectDirection", value=subjectDirection, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="formClass", value=formClass, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="IDCardType", value=IDCardType, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="birthPlace", value=birthPlace, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="politicalStatus", value=politicalStatus, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="nation", value=nation, cfsqltype="cf_sql_char" );
									
									queryObj.execute( sql=sql );
									
									/* 处理学籍数据 高考考生号默认为学号 */
			
									/* 处理学籍状态代码 */
									propAdvice.init();
									
									propAdvice.setRecognized( true ); /* 是否有学籍 */
									propAdvice.setRegister( true ); /* 是否电子注册 */
									propAdvice.setInSchool( true ); /* 是否在校 */

									academicType = "1";
									grade = rs_class.grade;
									studentLevel = rs_class.level_code;
									statusCode = "01";
									candidatesNo = "";
									joinDate = dateFormat(now(),"YYYYMMDD");
									instituteId = rs_class.institute_id;
									learningBranch = rs_class.b_id;
									studentType = "21";
									fosterType = "21";
									studentCompany = "";
									
									sql = "	INSERT INTO t_student_status(
												stu_id,
												acad_type,
												grade,
												level_code,
												status_code,
												candidatesno,
												date_join,
												homeaddr,
												zipcode,
												institute_id,
												subject_code,
												b_id,
												st_id,
												f_id,
												student_prop,
												stu_company
											) VALUES (
												:studentId,
												:academicType,
												:grade,
												:studentLevel,
												:statusCode,
												:candidatesNo, 
												:joinDate,
												:homeAddress,
												:zipCode,
												:instituteId,
												:subjectGB,
												:learningBranch,
												:studentType,
												:fosterType,
												:studentProps,
												:studentCompany  
											)";
											
									queryObj = new Query( datasource=application.dnsMaster );
									
									queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="academicType", value=academicType, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="studentLevel", value=studentLevel, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="statusCode", value=statusCode, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="candidatesNo", value=candidatesNo, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="joinDate", value=joinDate, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="homeAddress", value=homeAddress, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="zipCode", value=zipCode, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="instituteId", value= instituteId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="subjectGB", value=left(rs_class.subject_code, 6), cfsqltype="cf_sql_char" );
									queryObj.addParam( name="learningBranch", value=learningBranch, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="studentType", value=studentType, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="fosterType", value=fosterType, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="studentProps", value= propAdvice.stringProperty(), cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="studentCompany", value=studentCompany, cfsqltype="cf_sql_varchar" );
									
									queryObj.execute( sql=sql );
									
									
									sql = "INSERT INTO t_student_profile 
											(
												stu_id, 
												credit, credit_req, credit_opt, credit_phy, credit_com, credit_failure,
												credit_req_pt, credit_req_avgpt, 
												credit_pt, credit_avgpt,
												subject_rank, subject_idx, 
												subject_rank_all, subject_idx_all, 
												credit_grad, credit_grad_pt, credit_grad_avgpt
											) 
											VALUES 
											(
												:studentId, 
												0, 0, 0, 0, 0, 0, 
												0, 0, 
												0, 0, 
												0, 0, 0, 0, 
												0, 0, 0
											) ";
											
									queryObj = new Query( datasource=application.dnsMaster );
									queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.execute( sql=sql );
									
									/* 刷新行政班实际人数 */
			
									sql = "UPDATE t_class a 
											SET a.cls_scale = a.cls_scale + 1 
											WHERE a.cls_id = :classId ";
											
									queryObj = new Query( datasource=application.dnsMaster );
									
									queryObj.addParam( name="classId", value=formClass, cfsqltype="cf_sql_char" );
									
									queryObj.execute( sql=sql );
									
									/* 第二步 建立学生帐号 */
		
									sql = "INSERT INTO t_user (
												 user_id,
												 user_name,
												 user_email,
												 user_last_login,
												 user_last_login_ip  
											) VALUES (
													:UID,
													:userName,
													:email,
													:lastTimeLogin,
													:lastTimeIP 
											)";
											
									queryObj = new Query( datasource=application.dnsMaster );
									queryObj.addParam( name="UID", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="userName", value=studentName, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="email", value=studentId & "@email.unknown", cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="lastTimeLogin", value=datetimeAdvice.getCurrentDatetimeString(), cfsqltype="cf_sql_char" );
									queryObj.addParam( name="lastTimeIP", value="127.0.0.1", cfsqltype="cf_sql_varchar" );
									
									queryObj.execute( sql=sql );
									
									sequenceMatrixGroupUser.setUserId(studentId);
									sequenceMatrixGroupUser.setGroupId("P002");
									userGroupID = sequenceMatrixGroupUser.getID();
									
									sql = "INSERT INTO t_groupuser (
												ugid,
												user_id,
												group_id,
												date_create,
												date_lastupdate
											) VALUES (
												:userGroupID,
												:matrixUser,
												:matrixGroup,
												:created,
												:lastUpdate	
											)";
											
									queryObj = new Query( datasource=application.dnsMaster );
									queryObj.addParam( name="userGroupID", value=userGroupID, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="matrixUser", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="matrixGroup", value="P002", cfsqltype="cf_sql_char" );
									queryObj.addParam( name="created", value=datetimeAdvice.getCurrentDatetimeString(), cfsqltype="cf_sql_char" );
									queryObj.addParam( name="lastUpdate", value=datetimeAdvice.getCurrentDatetimeString(), cfsqltype="cf_sql_char" );
									
									queryObj.execute( sql=sql );
									
									changeId = sequenceStudentChange.getID();
									description="学生:" & studentName & " 转入班级：" & className;
									approveDate = dateFormat( now(), "YYYYMMDD" );
									
									sql = "	INSERT INTO t_stuchange (
												stuchg_id,
												change_date,
												approve_date,
												approve_docid,
												stu_source,
												stu_newlocation,
												op_description,
												institute_name,
												subject_name,
												grade,
												class_name,
												term_id,
												stu_id,
												admin_user_id,
												sct_code
											) VALUES(
												:changeId,
												:changeDate,
												:approveDate,
												:approveDocument,
												:source,
												:targetName,
												:description,
												'',
												'',
												'',
												'',
												:termId,
												:studentId,
												:adminId,
												:changeType 
											)";
											
									queryObj = new Query( datasource=application.dnsMaster );
									
									queryObj.addParam( name="changeId", value=changeId, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="changeDate", value=datetimeAdvice.getCurrentDatetimeString(), cfsqltype="cf_sql_char" );
									queryObj.addParam( name="approveDate", value=approveDate, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="approveDocument", value=approveDocument, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="source", value=source, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="targetName", value=targetName, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="description", value=description, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" );
									queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="adminId", value=tchId, cfsqltype="cf_sql_varchar" );
									queryObj.addParam( name="changeType", value=changeType, cfsqltype="cf_sql_char" );
									
									queryObj.execute( sql=sql );
									
									studentInsert++;
									
								</cfscript>
							</cfif>
							
						</cfloop>
					
					</cftransaction>
						
				</cfif>
			</cfif>
		</cfif> 
		
		<p>新增 [<cfoutput>#studentInsert#</cfoutput>] 条, 跳过数据 [<cfoutput>#studentSkip#</cfoutput>] 条,错误数据 [<cfoutput>#studentError#</cfoutput>] 条.</p>
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>